﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using Grishko.SerilizeTools;
using System.Reflection;
using Grishko.DataTier.MetaInfo;

namespace Grishko.DataTier.SqlSentence
{
    public class ScriptEngine:EntitySingle
    {
        public ScriptEngine() { }
        public  ScriptEngine(TScriptEngineSrchInfo Settings)
        {
            __EntityTypeInfo = Settings; 
        }
           

        public class TScriptEngineSrchInfo
        {
            private Hashtable _PropertyCollection = new Hashtable();

            public Type EntityType { get; set; }
            
            public string SqlFunction { get; set; }
            
            /// <summary>
            /// Перечень пар - свойство - соотв. тип. Добавляемые к спсику
            /// </summary>
            public Hashtable PropertyCollection { get { return _PropertyCollection; } }

            /// <summary>
            /// Свойства - исключаемые из списка доступных в операциях select
            /// </summary>
            public string[] BanProperties { get; set; }

        }
        
        public class TMandatoryConditions:EntityList
        {
            public void AddMandatoryCondition_Eq(String PropertyName, string _Value)
            {
                Word w = new Word() { WordType = Word.WordTypes.AND, Operator = Word.OperatorTypes.EQUALLY };
                w.Value = new WordFieldValue() { Value = _Value };
                w.PropertyName = PropertyName;
                this.Add(w);
            }

            public void AddMandatoryCondition_Eq(String PropertyName, WordFieldValue_TreeValue _Value)
            {
                Word w = new Word() { WordType = Word.WordTypes.AND, Operator = Word.OperatorTypes.EQUALLY };
                w.PropertyType = SrchItemInfo.PropertyControlType.Tree;
                w.Value =  _Value ;
                w.PropertyName = PropertyName;
                this.Add(w);
            }

            public void AddMandatoryCondition_Like(String PropertyName, WordFieldValue_TreeValue _Value)
            {
                Word w = new Word() { WordType = Word.WordTypes.AND, Operator = Word.OperatorTypes.LIKE};
                w.PropertyType = SrchItemInfo.PropertyControlType.Tree;
                w.Value = _Value;
                w.PropertyName = PropertyName;
                this.Add(w);
            }
            public void AddMandatoryCondition_Like(String PropertyName, string _Value)
            {
                Word w = new Word() { WordType = Word.WordTypes.AND, Operator = Word.OperatorTypes.LIKE };
                w.Value = new WordFieldValue() { Value = _Value };
                w.PropertyName = PropertyName;
                this.Add(w);
            }

            public void AddMandatoryCondition_Between(string PropertyName, DateTime  dtStart, DateTime  dtFinish)
            {
                Word w = new Word() { WordType = Word.WordTypes.AND, Operator = Word.OperatorTypes.BETWEEN };
                w.Value = new WordFieldValue_DateBetween() { StartValue = dtStart, EndValue = dtFinish };
                w.PropertyName = PropertyName;
                w.PropertyType = SrchItemInfo.PropertyControlType.Date;
                this.Add(w);
            }

            public override void Add(EntitySingle obj)
            {
                Word w = (obj as Word);
                w.ImTmp = true;
                base.Add(w);
            }

        }
        

        #region Константы скриптов
        const string Script_SrchRoot = "#SrchRoot";
        ///// <summary>
        ///// Sql функция - поставщик ObjectPropertyStorage=EntityType.SqlFunction
        /// </summary>
        protected string Script_SrchObjectPropertyStorage
        {
            get { return EntityTypeInfo.SqlFunction; }
        }

        public virtual string EntityTypeTagName
        {
            get
            {
                //Это поле пишется в базу поиска
                object[] arr = EntityTypeInfo.EntityType.GetCustomAttributes(typeof(TagNameInfo), true);
                if (arr.Length == 0)
                    throw new Exception(string.Format("ObjectWhereSentence.TagName нет TagNameInfo у типа {0}",
                                                       new string[] { __EntityTypeInfo.EntityType.ToString() }));

                return ((TagNameInfo)arr[0]).TagName;
            }

        }

        /// <summary>
        /// Paging StartPageRow 
        /// </summary>
        private int _StartPageRow = 1;
        public int StartPageRow { get { return _StartPageRow; } set { _StartPageRow = value; } }

        /// <summary>
        /// Paging  EndPageRow 
        /// </summary>
        private int _EndPageRow = 100;
        public int EndPageRow { get { return _EndPageRow; } set { _EndPageRow = value; } }

        #endregion  

        private TMandatoryConditions _MandatoryConditions = new TMandatoryConditions();

        /// <summary>
        /// Обязательные условия - колекция WhereSentenceWord
        /// </summary>
        public TMandatoryConditions MandatoryConditions
        {
            get { return _MandatoryConditions; }

        }

        readonly TScriptEngineSrchInfo __EntityTypeInfo = null; 
        


        private Sentence _Sentence = new Sentence();

        public Sentence Sentence { get { return _Sentence; } set { _Sentence = value; } }
        
        /// <summary>
        /// Объект - описывающий функцию - поставщика поискового пространства и коллекцию дополнительных объектов для поиска
        /// </summary>
        public  TScriptEngineSrchInfo EntityTypeInfo
        {
            get
            {
                return __EntityTypeInfo;

            }           
        }

        public string Lexeme { get; set; }
        /// <summary>
        ///   признак что в запросе учавствует непараметризированное условие 
        /// </summary>
        public bool IHaveOnlyValueSrchWord
        {
            get 
            {
                return Sentence.WordList.GetWords(SrchItemInfo.PropertyControlType.OnlyValueSrch).Count > 0;
                
            }
        }

        

        /// <summary>
        /// Удалить все временные условия, почистить специализированное хранилище врем. условий
        /// </summary>
        private void ClearTempCondition()
        {         

            List<string> l = new List<string>();
            for (int i = 0; i < Sentence.WordList.Count; i++)
            {
                if ((Sentence.WordList[i] as Word).ImTmp)
                    l.Add(Sentence.WordList[i].ID);
            }

            foreach (string s in l)
                Sentence.WordList.Remove(s);


            MandatoryConditions.Clear();
        }

        public virtual string GetScript()
        {            
            try
            {
                foreach (Word i in MandatoryConditions)
                {
                    Sentence.WordList.Add(i);
                }

                ///Если существует что нибудь в Lexeme - вставляем как усовие                
                foreach (Word  i in GetWhereSentenceWordsFromString(Lexeme))
                {
                    i.ImTmp = true;
                    this.Sentence.WordList.Add(i);
                }


                StringBuilder Writer = new StringBuilder();
                Script_DeclareVars_0(Writer);                        //Декларировать переменные

                /////если у запрос пустой - выбрать все объекты
                if (Sentence.WordList.Count == 0)
                {
                    Script_PopulateRootOfPropertyTree_1(Writer);
                    Writer.Append(" INSERT INTO @SelectedObjectId SELECT ObjectID FROM #SrchRoot ");
                    Script_PopulatePage_5(Writer);
                    Script_GetObjects_6(Writer);
                    return Writer.ToString();
                }


                Script_PopulateTreeValueFields_01(Writer);           // Срез по древовидным свойствам
                Script_PopulateRootOfPropertyTree_1(Writer);         //Вершина дерева
                Script_PopulatePropertyTree_2(Writer);               //Заполнение дерева
                Script_UpadateTreeValueProperty_3(Writer);           //Отметить - ok - значения древовидных свойств
                Script_PopulatePage_400(Writer);                     // Расчет функций  
                Script_PopulateTargetObjectSet_4(Writer);            //Конечная выборка ID                           

                //Непараметризированая часть Like - есть?            
                if (IHaveOnlyValueSrchWord)
                {
                    Script_PopulatePage_401(Writer);
                    Script_PopulatePage_41(Writer);                    
                }

                

                Script_PopulatePage_5(Writer);                       //Разбивка Id на страницы
                Script_GetObjects_6(Writer);                       //Конечная выборка объектов           

                return Writer.ToString();
            }
            catch 
            {
                return string.Empty;
            }
            finally
            {
                ClearTempCondition();
            }

        }


        #region Шаги скрипта
        /// <summary>
        /// Сформировать Корень дерева PropertyStorageTree
        /// </summary>
        /// <returns></returns>
        protected virtual void Script_DeclareVars_0(StringBuilder Writer)
        {            
            Writer.Append("DECLARE @PropertyArray TABLE (ObjectId varchar(36), 	Property varchar(max), ItemCount int)" + Environment.NewLine);
            Writer.Append("DECLARE @SelectedObjectId TABLE (ObjectId varchar(36))" + Environment.NewLine);
            Writer.Append("DECLARE @SelectedObjectId_ TABLE (ObjectId varchar(36))" + Environment.NewLine);            
            Writer.Append("CREATE TABLE #TreePropertyCollection   (Objectid varchar(36),TeeValueId varchar(36)) " + Environment.NewLine);
            Writer.Append("CREATE TABLE #PopulatedObjectTree (ObjectID varchar(36), PropertyName varchar(max) ,TxtProperty varchar(max), PropertyValue varchar(max),TxtPropertyValue varchar(max),ValueIsSqlFunction bit)" + Environment.NewLine);

        }

        /// <summary>
        /// Сформировать выборку по TreeValue полям 
        /// </summary>
        /// <returns></returns>
        protected virtual void Script_PopulateTreeValueFields_01(StringBuilder Writer)
        {
            Func<Word, string> GetFieldOrConditions = delegate(Word Field)
            {
                WordFieldValue_TreeValue TreeValue = (WordFieldValue_TreeValue)Field.Value;

                StringBuilder sb = new StringBuilder();
                sb.Append(" PropertyValue='" + TreeValue.Value.ID+ "' ");

                foreach (Grishko.DataTier.DictionaryItem item in TreeValue.Value.ChildNodes)
                {
                    sb.Append(" OR ");
                    sb.Append("PropertyValue='" + item.Value + "' ");

                }
                return sb.ToString();
            };

            ///выбираем - запрошенные свойства по TreePropertyValue
            List<Word> l = Sentence.WordList.ToList<Word>();
            var TreeFields =
                 from n in l
                 where
                             n.PropertyType == SrchItemInfo.PropertyControlType.Tree 
                             //||n.PropertyType == SrchItemInfo.PropertyControlType.TreeRB

                 select n;


            Word[] arr = TreeFields.ToArray<Word>();
            if (arr.Length == 0)
                return;

            List<string> s_arr = new List<string>();
            for (int i = 0; i < arr.Length; i++)
            {
                WordFieldValue_TreeValue TreeValue1 = (WordFieldValue_TreeValue)arr[i].Value;
                
                string sOr = GetFieldOrConditions(arr[i]);

                string sOpType = string.Empty;
                if (arr[i].Operator == Word.OperatorTypes.EQUALLY)
                    sOpType = "=" + (TreeValue1.Value.ChildNodes.Count + 1).ToString();
                else
                    sOpType = ">1";

                string[] ScriptParams = new string[] { TreeValue1.Value.ID, sOr, sOpType };
                string FormatString = " SELECT ObjectID,'{0}' FROM " + Environment.NewLine +
                                        "(SELECT ObjectID , 1 as ItemCount from vSrchObjectPropertyTreeStorage " + Environment.NewLine +
                                         "WHERE " + Environment.NewLine +
                                         " {1} " + Environment.NewLine +
                                         ") as res " + Environment.NewLine +
                                         "GROUP BY res.Objectid " + Environment.NewLine +
                                         "HAVING count(res.objectid) {2} " + Environment.NewLine;
                string si = string.Format(FormatString, ScriptParams);
                s_arr.Add(si);
            }

            Writer.Append("--Отобрать значения деревянных справочников " + Environment.NewLine);
            Writer.Append("INSERT INTO #TreePropertyCollection  ");

            for (int j = 0; j < s_arr.Count; j++)
            {
                if (j != 0)
                    Writer.Append(" UNION ");
                Writer.Append(s_arr[j] + Environment.NewLine);

            };
        }

        /// <summary>
        /// Сформировать Корень дерева PropertyStorageTree
        /// </summary>
        /// <returns></returns>
        protected virtual void Script_PopulateRootOfPropertyTree_1(StringBuilder Writer)
        {
            string sAddWhere = String.Empty;
            string[] ScriptParams = { Script_SrchRoot, Script_SrchObjectPropertyStorage, EntityTypeTagName, sAddWhere };
            string FormatString =
                                 "select distinct  " + Environment.NewLine +
                                 " ObjectID " + Environment.NewLine +
                                 " into " + Environment.NewLine +
                                 " {0} " + Environment.NewLine +
                                 " from " + Environment.NewLine +
                                 " {1} as Source " + Environment.NewLine +
                                 " where " + Environment.NewLine +
                                 " Source.TagName='{2}' " + Environment.NewLine +
                                 " {3} " + Environment.NewLine;
            Writer.Append(string.Format(FormatString, ScriptParams));

        }
               
        /// <summary>
        /// Заполнить дерево 
        /// </summary>
        /// <param name="Writer"></param>
        protected virtual void Script_PopulatePropertyTree_2(StringBuilder Writer)
        {
            #region GetNonIncludeObjects_Where
            ///выбрать из всех слов - слова 2 го уровня (объекты)- поля по которым идет запрос кроме слов являющимися функциями           
            Func<string> GetNonIncludeObjects_Where = delegate()
            {
                List<Word> l = Sentence.WordList.ToList<Word>();
                var Items =
                from n in l
                where
                    n.Level >1
                select n;

                List<string> res = new List<string>();
                //слова второго уровня по которым идет запрос
                Word[] SecondLevelWords = Items.ToArray<Word>();
                HashSet<string> hsSecondLevelWords = new HashSet<string>();
                foreach (Word i in SecondLevelWords)
                {
                    int dot = i.PropertyName.IndexOf(".");

                    hsSecondLevelWords.Add(i.PropertyName.Substring (0,dot));
                }
                
                //Все слова
                PropertyInfo[] FullList = EntityTypeInfo.EntityType.GetProperties();
                HashSet<string>  hsFullList = new  HashSet<string> ();
                foreach (PropertyInfo i in FullList)
                {
                    if (!ReflectionItem.MemberIsEntity(i))
                        continue;
                        hsFullList.Add(i.Name);
                }

                hsFullList.ExceptWith(hsSecondLevelWords);

                if (hsFullList.Count == 0)
                    return string.Empty;

                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < hsFullList.Count; i++)
                {
                    if (i != 0)
                        sb.Append(",");

                    sb.Append("'" + hsFullList.ToArray <string >() [i] + "'");

                }

                string[] Params = { sb.ToString() };
                string sFormat = " AND NOT srch.propertyname IN ({0})";
                return string.Format(sFormat, Params);
            };
            #endregion

            #region sPropWhere/sPropWhere_
            Func<string> GetPropWhere = delegate()
            {
                if(IHaveOnlyValueSrchWord) 
                    return string.Empty;
                string[] Templ = { "tree.PropertyName + '.' + ObjectPropVal. PropertyName = '{0}' AND  ObjectPropVal.TxtPropertyValue like '%{1}%'",                                
                               "tree.PropertyName + '.' + ObjectPropVal. PropertyName = '{0}' AND  ObjectPropVal.TxtPropertyValue ='{1}'",
                               "tree.PropertyName + '.' + ObjectPropVal. PropertyName = '{0}' ",
                               "ObjectPropVal.TxtPropertyValue LIKE '%{0}%' "};
                return  Script_PopulatePropertyTree_2_GetFieldsRestriction(Templ);

            };

            Func<string> GetPropWhere_ = delegate()
            {
                if (IHaveOnlyValueSrchWord)
                    return string.Empty;

                string[] Templ_ = { "VS. PropertyName = '{0}' AND  VS.TxtPropertyValue like '%{1}%'",                                
                               "VS. PropertyName = '{0}' AND  VS.TxtPropertyValue ='{1}'",
                               "VS. PropertyName = '{0}' ",
                               "VS. TxtPropertyValue LIKE '%{0}%' "};
                return  Script_PopulatePropertyTree_2_GetFieldsRestriction(Templ_);

            };
                
            #endregion

            string sPropWhere = GetPropWhere();
            sPropWhere = (sPropWhere.Length > 0 ? " AND " + sPropWhere : "");

            string sPropWhere_ = GetPropWhere_();
            sPropWhere_ = (sPropWhere_.Length > 0 ? " AND " + sPropWhere_ : "");


            /// Список тегов - исключений - формируется набор тегов - не учавствующих в запросе0
            string sNonIncludeObjects_Where = (IHaveOnlyValueSrchWord ? string .Empty :GetNonIncludeObjects_Where());

            string[] ScriptParams = { Script_SrchRoot, 
                                    Script_SrchObjectPropertyStorage, 
                                    EntityTypeTagName, 
                                    sNonIncludeObjects_Where,
                                    sPropWhere,
                                    sPropWhere_};


            string FormatString =
                                " ;WITH " + Environment.NewLine +
                                " TREE (ObjectID, PropertyName,TxtPropertyName, PropertyValue, TxtPropertyValue ,IsObject,LinkProp,ValueIsSqlFunction ) " + Environment.NewLine +
                                " AS ( " + Environment.NewLine +
                                "-- Корни дерева - записи свойства отвечающие уловиям отбора " + Environment.NewLine +
                                " SELECT " + Environment.NewLine +
                                " srch.ObjectID, srch.PropertyName, srch.TxtPropertyName, srch.PropertyValue,srch.TxtPropertyValue, srch.isobject, srch.PropertyValue as LinkProp, srch.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM " + Environment.NewLine +
                                "		{1}  srch       				" + Environment.NewLine +
                                "		JOIN {0} SrchRoot on SrchRoot.ObjectID =srch.ObjectID " + Environment.NewLine +
                                " WHERE " + Environment.NewLine +
                                "	srch.ISObject=1	{3}" + Environment.NewLine +
                                " UNION ALL" + Environment.NewLine +
                                "-- Подзапрос -  вниз" + Environment.NewLine +
                                " SELECT " + Environment.NewLine +
                                " T.ObjectID, " + Environment.NewLine +
                                " CAST((T.PropertyName +'.'+ V.PropertyName)   as varchar(255)) as PropertyName," + Environment.NewLine +
                                " CAST((T.TxtPropertyName +'.'+ V.TxtPropertyName)   as varchar(255)) as TxtPropertyName," + Environment.NewLine +
                                " V.PropertyValue, V.TxtPropertyValue, t.isobject, V.PropertyValue as LinkProp,  V.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM {1} V " + Environment.NewLine +
                                " JOIN TREE T " + Environment.NewLine +
                                " ON " + Environment.NewLine +
                                " T.PropertyValue= V.objectid " + Environment.NewLine +
                                " and V.isobject=1 )     " + Environment.NewLine +
                                " -- Получаем дерево объектов - и цепляем описательные свойства" + Environment.NewLine +
                                " INSERT INTO #PopulatedObjectTree  SELECT " + Environment.NewLine +
                                " tree.ObjectId,  " + Environment.NewLine +
                                " CAST((tree.PropertyName +'.'+ ObjectPropVal.Propertyname )   as varchar(255)) as PropertyName , " + Environment.NewLine +
                                " CAST((tree.TxtPropertyName+'.'+ ObjectPropVal.TxtPropertyName  )   as varchar(255)) as TxtProperty," + Environment.NewLine +
                                " ObjectPropVal.PropertyValue, " + Environment.NewLine +
                                " ObjectPropVal.TxtPropertyValue, " + Environment.NewLine +
                                " ObjectPropVal.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM tree  " + Environment.NewLine +
                                " join {1} ObjectPropVal on ObjectPropVal.objectid=tree.LinkProp" + Environment.NewLine +
                                " WHERE " + Environment.NewLine +
                                " ObjectPropVal.isobject=0" + Environment.NewLine +
                                "{4}" + Environment.NewLine +
                                " UNION " + Environment.NewLine +
                                " SELECT " + Environment.NewLine +
                                " VS.ObjectId,  " + Environment.NewLine +
                                " VS.PropertyName ," + Environment.NewLine +
                                " VS.TxtPropertyName,		" + Environment.NewLine +
                                " VS.PropertyValue, " + Environment.NewLine +
                                " VS.TxtPropertyValue, " + Environment.NewLine +
                                " VS.ValueIsSqlFunction " + Environment.NewLine +
                                " FROM " + Environment.NewLine +
                                " {1} VS JOIN {0} on VS.ObjectId={0}.ObjectId " + Environment.NewLine +
                                " WHERE " + Environment.NewLine +
                                " VS.isobject=0" + Environment.NewLine +
                                "{5}";

            Writer.Append(string.Format(FormatString, ScriptParams));


        }
        
        /// <summary>
        ///  В дереве - найти поодмножество id - отвечающих условиям отбора
        /// </summary>
        /// <param name="Writer"></param>
        protected virtual void Script_UpadateTreeValueProperty_3(StringBuilder Writer)
        {
            string FormatString = "--выделить подходящие древовидные свойства " + Environment.NewLine +
                                 "UPDATE #PopulatedObjectTree " + Environment.NewLine +
                                 "SET PropertyValue='OK' " + Environment.NewLine +
                                 "WHERE " + Environment.NewLine +
                                 "PropertyValue IN (SELECT TreePropertyCollection.Objectid+'.'+TreePropertyCollection.TeeValueId from #TreePropertyCollection TreePropertyCollection)";
            Writer.Append(FormatString);

        }
        
        /// <summary>
        ///  1.Отобрать все ид - входящие в or предложения 
        /// </summary>
        /// <param name="Writer"></param>
        protected virtual void Script_PopulateTargetObjectSet_4(StringBuilder Writer)
        {
            //Условия для полей входящих в или
            string sWhere = GetWhere(Word.WordTypes.OR, "Script_PopulateTargetObjectSet_4");
            if (sWhere.Length > 0)
            {
                Writer.Append("INSERT INTO @SelectedObjectId  SELECT ObjectId from #PopulatedObjectTree WHERE " + sWhere + Environment.NewLine);
                Writer.Append("DELETE FROM #PopulatedObjectTree  WHERE ObjectId in (SELECT ObjectId FROM  @SelectedObjectId  )" + Environment.NewLine);
            }
            // Формирвания по условиям AND           
            sWhere = GetWhere(Word.WordTypes.AND, SrchItemInfo.PropertyControlType.OnlyValueSrch, "Script_PopulateTargetObjectSet_4");
            if (sWhere.Length > 0)
            {
                List<Word> l = Sentence.WordList.GetWords(Word.WordTypes.AND, SrchItemInfo.PropertyControlType.OnlyValueSrch);
                string[] ScriptParams = { sWhere, l.Count.ToString() };
                string FormatString =
                                     "INSERT INTO @SelectedObjectId  " + Environment.NewLine +
                                     "SELECT " + Environment.NewLine +
                                     "Property.objectid	" + Environment.NewLine +
                                     "FROM " + Environment.NewLine +
                                     "#PopulatedObjectTree  Property" + Environment.NewLine +
                                     "WHERE {0} " + Environment.NewLine +
                                     "GROUP BY objectid " + Environment.NewLine +
                                     "HAVING count(objectid) ={1}" + Environment.NewLine;
                Writer.Append(string.Format(FormatString, ScriptParams));
            }


        }

        protected virtual void Script_PopulatePage_401(StringBuilder Writer)
        {
            string FormatString = "--Дать представления в виде текста всем  древовидны значениям" + Environment.NewLine +
            "UPDATE #PopulatedObjectTree SET TxtPropertyValue=SOPTS.ParentPropertyAsText FROM vSrchObjectPropertyTreeStorage SOPTS join #PopulatedObjectTree POT ON POT.TxtPropertyValue=SOPTS.OBJECTID+'.'+ SOPTS.PropertyValue WHERE ParentProperty is NULL ";
            Writer.Append(FormatString);
        }
        /// <summary>
        /// подсчитать значения функций 
        /// </summary>
        /// <param name="Writer"></param>
        protected virtual void Script_PopulatePage_400(StringBuilder Writer)
        {
            string FormatString = "-- посчитать значения функций " + Environment.NewLine +
                "-- Расчет функций " + Environment.NewLine +
                "DECLARE @ObjectID varchar(36)" + Environment.NewLine +
                "DECLARE @PropretyName Varchar(255)" + Environment.NewLine +
                "DECLARE @SQL nvarchar(500);" + Environment.NewLine +
                "DECLARE @SQL_ID nvarchar(500);" + Environment.NewLine +
                "DECLARE @ParmDefinition nvarchar(500);" + Environment.NewLine +
                "DECLARE @Result Varchar(max)" + Environment.NewLine +
                "DECLARE @CalculateResults TABLE (SqlFuncion varchar(max), SqlFuncionValue varchar(max))" + Environment.NewLine +
                "DECLARE function_cursor CURSOR FOR select ObjectID, PropertyName, PropertyValue  from #PopulatedObjectTree where ValueIsSqlFunction=1" + Environment.NewLine +
                "OPEN function_cursor " + Environment.NewLine +
                "FETCH NEXT FROM function_cursor " + Environment.NewLine +
                "INTO @ObjectID, @PropretyName,@SQL " + Environment.NewLine +
                "WHILE @@FETCH_STATUS = 0 " + Environment.NewLine +
                "BEGIN " + Environment.NewLine +
                "   SET @ParmDefinition = N'@ResultOUT varchar(max) OUTPUT'; " + Environment.NewLine +
                "   SET @SQL_ID=@SQL  " + Environment.NewLine +
                "   SET @SQL =N'SET @ResultOUT = '+ @SQL " + Environment.NewLine +
                "   EXECUTE sp_executesql @SQL,@ParmDefinition, @ResultOUT=@Result OUTPUT; " + Environment.NewLine +
                "   INSERT INTO @CalculateResults VALUES(@SQL_ID,@Result) " + Environment   .NewLine +
                "FETCH NEXT FROM function_cursor " + Environment.NewLine +
                "INTO @ObjectID, @PropretyName,@SQL " + Environment.NewLine +
                "END " + Environment.NewLine +
                "CLOSE function_cursor " + Environment.NewLine +
                "DEALLOCATE function_cursor" + Environment.NewLine +
                "UPDATE  POT " + Environment.NewLine +
                "SET " + Environment.NewLine +
                "    POT.PropertyValue=CR.SqlFuncionValue " + Environment.NewLine +
                "    ,POT.TxtPropertyValue=CR.SqlFuncionValue " + Environment.NewLine +
                "FROM " + Environment.NewLine +
                "    #PopulatedObjectTree POT JOIN @CalculateResults  CR " + Environment.NewLine +
                "ON " + Environment.NewLine +
                "   POT.PropertyValue=  CR.SqlFuncion " + Environment.NewLine;
            Writer.Append(FormatString);
        }

        protected virtual void Script_PopulatePage_41(StringBuilder Writer)
        {
            Func<string> f = delegate()
            {
                StringBuilder sbw = new StringBuilder();
                List<Word> l = Sentence.WordList.GetWords(SrchItemInfo.PropertyControlType.OnlyValueSrch);

                for (int i = 0; i < l.Count; i++)
                {
                    sbw.Append((i > 0 ? " OR " : "") + "(");
                    sbw.Append(l[i].GetSql(true,""));
                    sbw.Append(")");
                }
                string s = sbw.ToString();
                return s;
            };

            Func<bool> IHaveParams = delegate()
            {
                foreach (Word i in Sentence.WordList)
                {
                    if (i.PropertyType != SrchItemInfo.PropertyControlType.OnlyValueSrch)
                        return true;
                   
                }
                return false;
            };

            // если запрос имеет параметризированные параметры 
            string sWhere = GetWhere(SrchItemInfo.PropertyControlType.OnlyValueSrch, "Script_PopulatePage_41");
            string[] ScriptParams = { "WHERE" + sWhere };

            string FormatString =string .Empty ;
            if (IHaveParams())
                FormatString =
                "INSERT INTO  @SelectedObjectId_ " + Environment.NewLine +
                                "SELECT DISTINCT S.objectid  FROM #PopulatedObjectTree P join @SelectedObjectId S ON S.objectid=P.objectid  {0}";
            else
                FormatString ="INSERT INTO  @SelectedObjectId_ " + Environment.NewLine +
                                "SELECT DISTINCT P.objectid  FROM #PopulatedObjectTree P {0}" ;

            Writer.Append(string.Format(FormatString, ScriptParams));
        }

        protected virtual void Script_PopulatePage_5(StringBuilder Writer)
        {
            bool bSelectedObjectId = Sentence.WordList.GetWords (SrchItemInfo.PropertyControlType.OnlyValueSrch).Count > 0;
            string[] ScriptParams = {StartPageRow.ToString (),
                                    EndPageRow.ToString ()  ,
                                    (bSelectedObjectId?"@SelectedObjectId_":"@SelectedObjectId")
                                   };
            string FormatString =
                                 "DECLARE @Page_SelectedObjectId TABLE (ObjectId varchar(36))" + Environment.NewLine +
                                 "INSERT INTO @Page_SelectedObjectId SELECT ObjectId	FROM  " + Environment.NewLine +
                                 "(SELECT  " + Environment.NewLine +
                                 "ObjectId, " + Environment.NewLine +
                                 "ROW_NUMBER()  OVER (ORDER BY ObjectId) AS row_num " + Environment.NewLine +
                                 "FROM " + Environment.NewLine +
                                 "{2}) " + Environment.NewLine +
                                 "AS Page 			" + Environment.NewLine +
                                 "WHERE row_num BETWEEN  {0} and {1}" + Environment.NewLine;
            Writer.Append(string.Format(FormatString, ScriptParams));
        }

        protected virtual void Script_GetObjects_6(StringBuilder Writer)
        {
            string FormatString =
                                "DECLARE @OutputRwCnt AS INT " + Environment.NewLine +
                                "SELECT @OutputRwCnt=COUNT(*) FROM @Page_SelectedObjectId      " + Environment.NewLine +
                                "DECLARE @res AS XML " + Environment.NewLine +
                                "SELECT @res=(SELECT vSrchStorageLinks.ObjectXML.query('.')  FROM @Page_SelectedObjectId T join vSrchStorageLinks on vSrchStorageLinks.ID=T.objectId FOR XML PATH(''), TYPE, ELEMENTS , ROOT('EntityList'))" + Environment.NewLine +
                                "IF not(@res is null)   SET @res.modify('insert attribute OutputRwCnt {sql:variable(''@OutputRwCnt'')} into (/EntityList)[1]')" + Environment.NewLine +
                                "select @res " + Environment.NewLine;
            Writer.Append(FormatString);
        }
       
        #endregion

        #region инструменты скрипта

        /// <summary>       
        /// Вернуть условие AND (tree.PropertyName + '.' + ObjectPropVal. PropertyName = 'Number' AND  ObjectPropVal.PropertyValue = '125')
        /// ObjectPropVal- добавляется если это like или =       
        /// Templates[0]=LikeTempl ;
        /// Templates[1]=EqualTempl ;
        /// DefTempl[2]=DefTempl;
        /// </summary>
        /// <returns></returns>
        private string Script_PopulatePropertyTree_2_GetFieldsRestriction(string[] Templates)
        {
            string LikeTempl = Templates[0];
            string EqualTempl = Templates[1];
            string DefTempl = Templates[2];
            string OnlyValueSrch = Templates[3];
            

            StringBuilder sbw = new StringBuilder();
            //Список полей  - которых  касается запрос (кроме функций)
            List<Word> l = Sentence.WordList .ToList <Word>();
            string sExpression = string.Empty;
            for (int i = 0; i < l.Count; i++)
            {

                if (!l[i].ImFuncton) // если слово не функция
                {
                    switch (l[i].Operator)
                    {
                        case Word.OperatorTypes.LIKE:
                            sExpression = string.Format(LikeTempl, new string[] { l[i].PropertyName, l[i].Value.ToString() });
                            break;
                        case Word.OperatorTypes.EQUALLY:
                            sExpression = string.Format(EqualTempl, new string[] { l[i].PropertyName, l[i].Value.ToString() });
                            break;
                        default:
                            sExpression = string.Format(DefTempl, new string[] { l[i].PropertyName, l[i].Value.ToString() });
                            break;
                    }

                    //Если - это непараметризированный запрос - шаблон один 
                    if (l[i].PropertyType == SrchItemInfo.PropertyControlType.OnlyValueSrch)
                        sExpression = string.Format(OnlyValueSrch, new string[] { l[i].Value.ToString() });


                    //Если - это дерево - шаблон один 
                    if (l[i].PropertyType == SrchItemInfo.PropertyControlType.Tree)
                        sExpression = string.Format(DefTempl, new string[] { l[i].PropertyName });

                }
                else
                {
                    sExpression = string.Format(OnlyValueSrch, new string[] { l[i].PropertyName });

                }

                    sbw.Append((i > 0 ? " OR " : "") + "(");
                    sbw.Append(sExpression);
                    sbw.Append(")");
                
            }
            return sbw.ToString();
        }

        private string GetWhere(Word.WordTypes Type, string tagPref)
        {
            return GetWhere(Type, tagPref);
        }
        private string GetWhere(Word.WordTypes  Type, string SourceName, string tagPref)
        {

            //Форммируем запрос по полям или
            StringBuilder sbw = new StringBuilder();
            List<Word> l = Sentence.WordList.GetWords (Type);
            for (int i = 0; i < l.Count; i++)
            {
                sbw.Append((i > 0 ? " OR " : "") + "(");
                sbw.Append(l[i].GetSql(true, tagPref));
                sbw.Append(")");
            }
            string s = sbw.ToString();
            if (SourceName.Equals(string.Empty))
                return s;
            else
            {
                s = s.Replace("PropertyName", SourceName + ".PropertyName");
                s = s.Replace("PropertyValue", SourceName + ".PropertyValue");
                return s;
            }
        }

        ///// <summary>
        ///// Строит запрос where - исключая уловия только по значению поля
        ///// </summary>
        ///// <param name="Type"></param>
        ///// <param name="SourceName"></param>
        ///// <returns></returns>
        private string GetWhere( Word.WordTypes Type, SrchItemInfo.PropertyControlType ExludeField, string tagPref)
        {
            //Формируем запрос по полям или
            StringBuilder sbw = new StringBuilder();
            List<Word> l = Sentence.WordList.GetWords (Type,ExludeField);                
            for (int i = 0; i < l.Count; i++)
            {
                sbw.Append((i > 0 ? " OR " : "") + "(");
                sbw.Append(l[i].GetSql(true, tagPref));
                sbw.Append(")");
            }
            string s = sbw.ToString();
            return s;

        }

        private string GetWhere(SrchItemInfo.PropertyControlType FieldType, string tabPref)
        {
            StringBuilder sbw = new StringBuilder();
            List<Word> l = Sentence.WordList.GetWords (FieldType);

            for (int i = 0; i < l.Count; i++)
            {
                sbw.Append((i > 0 ? " OR " : "") + "(");
                sbw.Append(l[i].GetSql(true,tabPref));
                sbw.Append(")");
            }
            string s = sbw.ToString();
            return s;

        }

        /// <summary>
        /// Формирует набор слов ипа
        /// </summary>
        /// <returns></returns>
        private static List<Word> GetWhereSentenceWordsFromString(string value)
        {
            List<Word> result = new List<Word>();
            if (string.IsNullOrEmpty(value))
                                return result;

            value =  value.Trim().Replace(" ", "%") ;
            
            Word w = new Word() { WordType = Word.WordTypes.AND };
            w.Operator = Word.OperatorTypes.LIKE;
            w.PropertyType = SrchItemInfo.PropertyControlType.OnlyValueSrch;
            w.Value = new WordFieldValue() { Value = value };
            result.Add(w);
            return result;

            //List<Word> result = new List<Word>();
            //if (string.IsNullOrEmpty(value))
            //                            return result;

            //string[] arr = value.Split(" ".ToCharArray()[0]);
            //for (int i = 0; i < arr.Length; i++)
            //{
            //    if (arr[i] == " ")
            //        continue;
            //    Word w = new Word() { WordType = Word.WordTypes.AND};
            //    w.Operator = Word.OperatorTypes.LIKE; 
            //    w.PropertyType = SrchItemInfo.PropertyControlType.OnlyValueSrch;
            //    w.Value = new WordFieldValue() { Value = arr[i] };                
            //    result.Add(w);
            //}
            //return result;

        }

        #endregion


        #region Обработка типа

        /// <summary>
        /// Пройтись по типу и сформировать набор возможных полей предложения
        /// </summary> 
        public List<Word> GetSentenceDictionary()
        {
            Func<string, bool> InBanList = delegate(string PropertyName)
            {
                foreach (string s in this.EntityTypeInfo.BanProperties)
                {
                    if (PropertyName.IndexOf(s, 0) > -1)
                        return true;
                }
                return false;
            };

            List<Word> Result = SrchInfo.GetSentenceDictionary(__EntityTypeInfo.EntityType, this.EntityTypeInfo.BanProperties);

            ///Пройтись и сформировать дополнительные поисковые свойства                      
            foreach (DictionaryEntry itm in this.EntityTypeInfo.PropertyCollection)
            {
                foreach (Word f in SrchInfo.GetSentenceDictionary((Type)itm.Value))
                {
                    if (InBanList(f.PropertyName))
                        continue;

                    string sKey = string.Empty;
                    string sTxt = string.Empty;
                    string s = itm.Key.ToString();

                    int dif_pos=s.IndexOf ("/");
                    if (dif_pos > 0)
                    {
                        sKey = s.Substring(0, dif_pos);
                        sTxt = s.Substring(dif_pos+1);
                    }
                        

                    f.PropertyName = sKey + "." + f.PropertyName;
                    f.TxtKey = sTxt + "." + f.TxtKey;
                    Result.Add(f);
                }

            }
            return Result;

        }

        public Word GetSentenceDictionaryField(string PropertyName, Word.OperatorTypes OperationType)
        {
            Word result = null;
            result = GetSentenceDictionaryField(PropertyName);
            SetValueType(result, OperationType);
            return result;


        }

        /// <summary>
        /// Установить Value - соответсвующее опреации
        /// </summary>
        /// <param name="Field"></param>
        /// <param name="OperationType"></param>
        public static void SetValueType(Word  Field, Word.OperatorTypes OperationType)
        {
            switch (Field.PropertyType)
            {
                case SrchItemInfo.PropertyControlType.Text:
                    Field.Value = new WordFieldValue_StringBetween();
                    break;

                case SrchItemInfo.PropertyControlType.Number:
                    if (OperationType == Word.OperatorTypes.BETWEEN)
                        Field.Value = new WordFieldValue_NumberBetween();
                    else
                        Field.Value = new WordFieldValue_Number();
                    break;

                case SrchItemInfo.PropertyControlType.Date:
                    if (OperationType == Word.OperatorTypes.BETWEEN)
                        Field.Value = new WordFieldValue_DateBetween();
                    else
                        Field.Value = new WordFieldValue_Date();
                    break;

                case SrchItemInfo.PropertyControlType.Bool:
                    Field.Value = new WordFieldValue_Bool();
                    break;

                case SrchItemInfo.PropertyControlType.Tree :
                    Field.Value = new WordFieldValue_TreeValue ();
                    break;
            }
            Field.Operator = OperationType;
        }



        public Word GetSentenceDictionaryField(string PropertyName)
       {
           Word result = null;
           List<Word> Res = GetSentenceDictionary();
           for (int i = 0; i < Res.Count; i++)
           {
               if (Res[i].PropertyName == PropertyName)
               {
                   result = Res[i];
                   break;
               }
           }
           return result;               

       }
        


        #endregion



       
    }
}
